/**
 *
 */
package com.elcom.emocbc.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import com.elcom.emocbc.cbm.BroadcastDTO;
import com.elcom.emocbc.cbm.CellBroadcastService;
import com.elcom.emocbc.cbm.MenuItemDTO;
import com.elcom.emocbc.dbmapping.persistence.DbAccess;
import org.apache.commons.dbutils.DbUtils;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @author tiamo114
 *
 */
public class CellBroadcastDAO {

    private final Logger logger = LoggerFactory.getLogger(CellBroadcastDAO.class);
//    public static String SQL_GET_LIST_BROADCAST = "SELECT a.broadcasttime_id, a.zone_id, a.createdby, a.createddate, d.program_id, b.topic_id, c.zonename, a.starttime, a.program_indexs, a.cp_topic_id FROM broadcasttime a, cp_topic b, zones c, programs d where a.zone_id=c.zone_id and a.status=0 and d.status=1 and a.cp_topic_id=b.cp_topic_id and (a.cp_topic_id=d.cps_topic_id and a.program_indexs=d.indexs) and to_date(TO_CHAR(SYSDATE,'dd/MM/yyyy')||' '||a.starttime,'dd/MM/yyyy hh24:mi') between SYSDATE and SYSDATE+(10/1440) FOR UPDATE";
    public static String SQL_GET_LIST_BROADCAST = "SELECT bt.broadcasttime_id "
            + "	,bt.zone_id "
            + "	,bt.createdby "
            + "	,bt.createddate "
            + "	,p.program_id "
            + "	,cpt.topic_id "
            + "	,z.zone_name "
            + "	,bt.starttime "
            + "	,bt.program_indexs "
            + "	,bt.cp_topic_id "
            + "	,p.subcontents "
            + "	,cp.cpname "
            + "	,t.topicname "
            + " ,cp.cp_id "
            + " ,p.chain_id "
            + " ,bt.category_id "
            + " ,bt.schedule_id "
            + "FROM broadcasttime bt "
            + "	,cp_topic cpt "
            + "	,zone z "
            + "	,programs p "
            + "	,cp cp "
            + "	,topic t "
            + "WHERE bt.zone_id = z.zone_id "
            + "	AND bt.STATUS != 5 "
            + "	AND p.STATUS = 1 "
//            + "	AND trunc(p.timeplug) = trunc(sysdate) "
            + "	AND bt.cp_topic_id = cpt.cp_topic_id "
            + "	AND ( "
            + "		bt.cp_topic_id = p.cps_topic_id "
            + "		AND bt.program_indexs = p.indexs "
            + "		) "
            + "	AND cpt.cp_id = cp.cp_id "
            + "	AND t.topic_id = cpt.topic_id "
            + "	AND starttime = ? "
            + "FOR UPDATE";
    private static final String SQL_CALCULATE_TIME = "SELECT * "
            + "FROM ( "
            + "	SELECT * "
            + "	FROM broadcasttime a "
            + "	WHERE a.starttime > ? "
            + "	ORDER BY a.starttime "
            + "	) "
            + "WHERE ROWNUM = 1";
    private static final String SQL_GET_LIST_MENU = "SELECT * "
            + "FROM menu_items a "
            + "WHERE a.parent_id = ? "
            + "ORDER BY a.indexs";
    private static final String SQL_GET_LIST_PROGRAM = "SELECT a.program_id "
            + "FROM programs a "
            + "WHERE a.STATUS = 1 "
            + "	AND a.cps_topic_id = ? "
            + "	AND a.indexs = ?";
    private static final String SQL_GET_DISPLAY_CONTENT = "SELECT a.subcontents "
            + "FROM programs a "
            + "WHERE a.program_id = ?";
    private static final String SQL_GET_BROADCAST_HIST_SEQ = "select broadcast_hist_seq.NEXTVAL from dual";
    private static final String SQL_INSERT_HISTORY_BROADCAST = "INSERT INTO broadcast_hist ( "
            + "	broadcasttime_id "
            + "	,program_indexs "
            + "	,cp_topic_id "
            + "	,zone_id "
            + "	,starttime "
            + "	,STATUS "
            + "	,createdby "
            + "	,createddate "
            + "	,subcontents "
            + "	,broadcast_hist_id "
            + " ,topic_id "
            + " ,topic_name "
            + " ,program_id "
            + " ,cp_id "
            + " ,cp_name "
            + " ,schedule_id "
            + " ,zone_name "
            + "	) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
    private static final String SQL_INSERT_BROADCAST_CBC_TRANSACTION_MAP = "INSERT INTO CBC_TRANSACTION_MAP ( "
            + "	broadcast_id "
            + "	,cbc_transaction_id "
            + "	,created_time "
            + "	) values(?,?, to_date(?, 'dd-mm-yyyy hh24:mi:ss'))";
    private static final String SQL_UPDATE_STATE_BROADCAST = "update broadcasttime a set a.status=? where a.broadcasttime_id=?";
    private static final String SQL_DELETE_BROADCAST = "DELETE FROM broadcasttime where broadcasttime_id=?";
//    private static final String SQL_UPDATE_STATE_BROADCAST_HIST = "update broadcast_hist a set a.status=? where a.broadcast_hist_id=?";
    private static final String SQL_GET_LIST_ALL_MENU = "SELECT * FROM menu_items a where a.program_id=?";
//    private static final String SQL_NEXT_BROADCAST_HIST_SEQ = "SELECT broadcast_hist_seq.NEXTVAL FROM dual";
    private static final String SQL_INSERT_MENU_HIST = "insert into menu_items_hist(MENUITEMS_ID,PARENT_ID,BROADCAST_HIST_ID,ACTIONTYPE,SYNTAX,CONTENT,SHORTCODE,LEVELS,TITLE,PROGRAM_ID,INDEXS) values(?,?,?,?,?,?,?,?,?,?,?)";
    private SimpleDateFormat f_short = null;
    private SimpleDateFormat f_full = null;

    public CellBroadcastDAO() {
        f_short = new SimpleDateFormat("dd/MM/yyyy");
        f_full = new SimpleDateFormat("dd/MM/yyyy HH:mm");
    }

    private boolean updateStateBroadcast(Connection _conn, BroadcastDTO broadcast, int state) {
        Connection conn = _conn;
        PreparedStatement ps = null;

        try {
            ps = conn.prepareStatement(SQL_UPDATE_STATE_BROADCAST);
            ps.setInt(1, state);
            ps.setInt(2, broadcast.getBroadcastID());

            int i = ps.executeUpdate();
            if (i > 0) {
                logger.debug(String.format("Update OK with new state: %s", state));
                return true;
            } else {
                logger.warn(String.format("Cannot update with new state: %s", state));
            }

        } catch (Exception e) {
            logger.error("Exception when updateStateBroadcast to DB: ", e);
        } finally {
            DbUtils.closeQuietly(ps);
        }

        return false;
    }

    public BroadcastDTO getFirstProgramOfTriviaChain(String chainId) throws SQLException {
        BroadcastDTO result;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        final String SQL = "select program_id, subcontents from programs where chain_id = ? and indexs = 1 and status = 1";
        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL);
            ps.setString(1, chainId);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = new BroadcastDTO();
                result.setProgramID(rs.getString(1));
                result.setIndex(1);
                result.setSubContent(rs.getString(2));
                return result;
            } else {
                throw new SQLException("Cannot program_id from program for chain_id " + chainId);
            }

        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }
    }

    public int calculteTimeDistance(String startTime) {
        int rsTime = 60; // default la 1phut
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_CALCULATE_TIME);
            ps.setString(1, startTime);
            rs = ps.executeQuery();

            if (rs.next()) {
                String endTime = rs.getString("starttime");
                String start = f_short.format(new Date(System.currentTimeMillis())) + " " + startTime;
                String end = f_short.format(new Date(System.currentTimeMillis())) + " " + endTime;

                Date startDate = f_full.parse(start);
                Date endDate = f_full.parse(end);

                rsTime = (int) ((endDate.getTime() - startDate.getTime()) / 1000);
            }
        } catch (Exception e) {
            logger.error("Exception when calculate distance time from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }

        return rsTime;
    }

    public List<BroadcastDTO> getListBroadcast() {
        logger.debug("Test broadcast from DB");
        List<BroadcastDTO> list = new ArrayList<BroadcastDTO>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String starttime = new SimpleDateFormat("HH:mm").format(DateTime.now().plusMinutes(2).toDate());
        logger.debug("Get broadcast from DB at {}", starttime);

        try {
            conn = DbAccess.getInstance().getConnection();
            stmt = conn.prepareStatement(SQL_GET_LIST_BROADCAST);
            
            stmt.setString(1, starttime);
//			stmt.setQueryTimeout(3);
            rs = stmt.executeQuery();
            while (rs.next()) {
                BroadcastDTO broadcast = new BroadcastDTO();
                broadcast.setTopicID(rs.getInt("topic_id"));
                broadcast.setProgramID(rs.getString("program_id"));
                broadcast.setZone(rs.getString("zone_name"));
                String start = f_short.format(new Date(System.currentTimeMillis())) + " " + rs.getString("starttime");
                Date startDate = f_full.parse(start);
                broadcast.setStartDate(startDate);
                broadcast.setStartTime(rs.getString("starttime"));
                broadcast.setEndDate(new Date(startDate.getTime() + 60000l));
                broadcast.setCpTopicId(rs.getInt("cp_topic_id"));
                broadcast.setIndex(rs.getInt("program_indexs"));

                broadcast.setBroadcastID(rs.getInt("broadcasttime_id"));
                broadcast.setZoneID(rs.getLong("zone_id"));
                broadcast.setCreatedBy(rs.getString("createdby"));
                broadcast.setCreatedDate(rs.getDate("createddate"));
                broadcast.setCpName(rs.getString("cpname"));
                broadcast.setSubContent(rs.getString("subcontents"));
                broadcast.setTopicName(rs.getString("topicname"));
                broadcast.setCpId(rs.getInt("cp_id"));
                broadcast.setChainId(rs.getString("chain_id"));
                broadcast.setCategoryId(rs.getInt("category_id"));
                broadcast.setScheduleId(String.valueOf(rs.getInt("schedule_id")));
                if (updateStateBroadcast(conn, broadcast, CellBroadcastService.PROCESSING)) {
                    list.add(broadcast);
                    logger.info("Get broadcast request from DB and update state: " + broadcast.toString());
                }
            }
            conn.commit();
            logger.debug("Finish Get broadcast from DB");
        } catch (Exception e) {
            logger.error("Exception when get list broadcast from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, stmt, rs);
        }

        return list;
    }

    public List<MenuItemDTO> getListMenuItem(int topicID, String programID) {
        List<MenuItemDTO> list = new ArrayList<MenuItemDTO>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_GET_LIST_MENU);
            ps.setString(1, programID);
            rs = ps.executeQuery();

            while (rs.next()) {
                int shortcode = rs.getInt("shortcode");
                String type;

                String syntax = rs.getString("syntax");
                if (syntax == null) {
                    syntax = programID;
                    type = "B3";
                } else {
                    type = "A3";
                }

                MenuItemDTO menu = new MenuItemDTO(topicID, programID, type, shortcode, syntax, rs.getString("title"));
                list.add(menu);
                logger.info("Get menuItem from DB: " + menu.toString());
            }

        } catch (Exception e) {
            logger.error("Exception when get list menuItem from DB with programID-" + programID + " and topicID-" + topicID + ": ", e);
        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }

        return list;
    }

    public LinkedList<String> getListProgramID(int topicID) {
        LinkedList<String> list = new LinkedList<String>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_GET_LIST_PROGRAM);
            ps.setInt(1, topicID);
            rs = ps.executeQuery();
            while (rs.next()) {
                String programID = rs.getString("program_id");
                list.add(programID);
                logger.info("Get broadcast programID from DB: " + programID);
            }
        } catch (Exception e) {
            logger.error("Exception when get broadcast programID from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }

        return list;
    }

    public String getDisplayContent(String programID) {
        String result = "";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_GET_DISPLAY_CONTENT);
            ps.setString(1, programID);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getString("subcontents");
            }
        } catch (Exception e) {
            logger.error("Exception when get display content from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }
        return result;
    }

    public boolean insertBroadcastHist(BroadcastDTO broadcast, String subContent, int result) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            int broadcastHistId = getBroadcastHistSeq();
            logger.debug("Got new broadcast_hist_id = " + broadcastHistId);

            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_INSERT_HISTORY_BROADCAST);
            ps.setInt(1, broadcast.getBroadcastID());
            ps.setInt(2, broadcast.getIndex());
            ps.setInt(3, broadcast.getCpTopicId());
            ps.setLong(4, broadcast.getZoneID());
            ps.setTimestamp(5, new Timestamp(broadcast.getStartDate().getTime()));
            ps.setInt(6, result);
            ps.setString(7, broadcast.getCreatedBy());
            ps.setDate(8, broadcast.getCreatedDate());
            ps.setString(9, subContent);
            ps.setInt(10, broadcastHistId);
            ps.setInt(11, broadcast.getTopicID());
            ps.setString(12, broadcast.getTopicName());
            ps.setString(13, broadcast.getProgramID());
            ps.setInt(14, broadcast.getCpId());
            ps.setString(15, broadcast.getCpName());
            ps.setString(16, broadcast.getScheduleId());
            ps.setString(17, broadcast.getZone());
            int i = ps.executeUpdate();
            conn.commit();

            insertBroadcastMenu(broadcast, subContent, broadcastHistId);

            if (i > 0) {
                logger.info("Insert insertBroadcastHist OK with programID: " + broadcast.toString());
                return true;
            } else {
                logger.warn("Cannot insertBroadcastHist with programID: " + broadcast.toString());
            }

        } catch (Exception e) {
            logger.error("Exception when insertBroadcastHist to DB: ", e);
        } finally {
            DbUtils.closeQuietly(ps);
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    public void insertBroadcastMenu(BroadcastDTO broadcast, String subContent, int broadcastHistId) {
        Connection conn = null;
        PreparedStatement ps = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = DbAccess.getInstance().getConnection();
            psmt = conn.prepareStatement(SQL_GET_LIST_ALL_MENU);
            psmt.setString(1, broadcast.getProgramID());
            rs = psmt.executeQuery();
            while (rs.next()) {
                try {
                    ps = conn.prepareStatement(SQL_INSERT_MENU_HIST);
                    ps.setString(1, rs.getString("MENUITEMS_ID"));
                    ps.setString(2, rs.getString("PARENT_ID"));
                    ps.setInt(3, broadcastHistId);
                    ps.setString(4, rs.getString("ACTIONTYPE"));
                    String syntax = "";
                    if (rs.getString("SYNTAX") != null) {
                        syntax = rs.getString("SYNTAX");
                    }
                    ps.setString(5, syntax);

                    String content = "";
                    if (rs.getString("CONTENT") != null) {
                        content = rs.getString("CONTENT");
                    }
                    ps.setString(6, content);

                    ps.setString(7, rs.getString("SHORTCODE"));
                    ps.setInt(8, rs.getInt("LEVELS"));
                    ps.setString(9, rs.getString("TITLE"));
                    ps.setString(10, broadcast.getProgramID());
                    ps.setInt(11, rs.getInt("INDEXS"));

                    int i = ps.executeUpdate();
                    conn.commit();
                    if (i > 0) {
                        logger.info("Insert OK menu history for broadcast: " + broadcast.toString());
                    } else {
                        logger.info("Cannot insert menu history for broadcast: " + broadcast.toString());
                    }
                } catch (Exception e) {
                    logger.error("Exception when insertBroadcastMenu to DB: ", e);
                } finally {
                    DbUtils.close(ps);
                    ps = null;
                }
            }
        } catch (Exception e) {
            logger.error("Exception when get all Menu from DB: ", e);
        } finally {
            DbUtils.closeQuietly(conn, psmt, rs);
        }
    }

    public int deleteBroadcast(BroadcastDTO broadcast) {
        int result = 0;
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_DELETE_BROADCAST);
            ps.setInt(1, broadcast.getBroadcastID());

            result = ps.executeUpdate();
            conn.commit();
            if (result > 0) {
                logger.debug("Delete OK broadcasttimeId: " + broadcast.getBroadcastID());
            } else {
                logger.warn("Cannot delete broadcasttimeId: " + broadcast.getBroadcastID());
            }

        } catch (Exception e) {
            logger.error("Exception when delete broadcasttimeId: " + broadcast.getBroadcastID() + ": ", e);
        } finally {
            DbUtils.closeQuietly(ps);
            DbUtils.closeQuietly(conn);
        }

        return result;
    }

    public boolean updateStateBroadcast(BroadcastDTO broadcast, int state) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_UPDATE_STATE_BROADCAST);
            ps.setInt(1, state);
            ps.setInt(2, broadcast.getBroadcastID());

            int i = ps.executeUpdate();
            conn.commit();
            if (i > 0) {
                logger.debug("Update OK with new state: " + state);
                return true;
            } else {
                logger.warn("Cannot update with new state: " + state);
            }

        } catch (Exception e) {
            logger.error("Exception when updateStateBroadcast to DB: ", e);
        } finally {
            DbUtils.closeQuietly(ps);
            DbUtils.closeQuietly(conn);
        }

        return false;
    }

    private int getBroadcastHistSeq() throws SQLException {
        int result;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_GET_BROADCAST_HIST_SEQ);
            rs = ps.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
                return result;
            } else {
                throw new SQLException("Cannot get next seq for broadcast_hist_id");
            }

        } finally {
            DbUtils.closeQuietly(conn, ps, rs);
        }

    }

    public void insertCbcTransactionMap(int broadcastId, String cbcTransactionId) {
        Connection conn = null;
        PreparedStatement ps = null;

        try {
            conn = DbAccess.getInstance().getConnection();
            ps = conn.prepareStatement(SQL_INSERT_BROADCAST_CBC_TRANSACTION_MAP);
            ps.setInt(1, broadcastId);
            ps.setString(2, cbcTransactionId);
            SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
            ps.setString(3, sdf.format(new Date()));

            int i = ps.executeUpdate();
            conn.commit();
            if (i > 0) {
                logger.info("Insert OK Cbc transactionID " + cbcTransactionId + " for broadcast: " + broadcastId);
            } else {
                logger.info("Cannot insert Cbc transactionID " + cbcTransactionId + " for broadcast: " + broadcastId);
            }

        } catch (Exception e) {
            logger.error("Exception when insertCbcTransactionMap to DB: ", e);
        } finally {
            DbUtils.closeQuietly(ps);
            DbUtils.closeQuietly(conn);
        }
    }
}
